﻿-- CREATE TRIGGER trgchange_quotaion_info
--   AFTER INSERT OR UPDATE or DELETE
--   ON sale_order
--   FOR EACH ROW
--   EXECUTE PROCEDURE trgfnupdate_quotation();
--############## Not Done ########################-
CREATE OR REPLACE FUNCTION trgfnupdate_quotation_change_owner_project_info()
                        RETURNS trigger AS
                        $BODY$
                        DECLARE
				using_id integer;
                        Begin
                        -- ### Begin Update Trigger ###
                            If TG_OP='UPDATE' then
			--########## Mechanical ##########
				if (new.project_m_id is null and old.project_m_id is not null) or
				   (new.project_m_id is not null and old.project_m_id is null) or
				   (new.project_m_id <>old.project_m_id) or
				   
					using_id=new.project_m_id;
					Update kdvn_project set project_amount=(Select
											sum(coalesce(q_amount_total_m,0)) as project_amount
										from 
											sale_order
										where 
											state='done' and project_m_id=using_id
										group by 
											project_m_id)
					where id=using_id;
					Update kdvn_project set project_vat=(Select
											sum(coalesce(q_amount_total_m,0))*max(coalesce(taxes_percent,0))/100 as project_vat
										from 
											sale_order
										where 
											state='done' and project_m_id=using_id
										group by 
											project_m_id)
					where id=using_id;
					Update kdvn_project set project_total=(Select
											sum(coalesce(q_amount_total_m,0))*(max(coalesce(taxes_percent,0))/100+1)
										from 
											sale_order
										where 
											state='done' and project_m_id=using_id
										group by 
											project_m_id)
					where id=using_id;
				end if;
				-- ## Update Project Amount, VAT, Total M when change Project ID ##
				if 
					(new.project_m_id<>old.project_m_id) or
					(new.project_m_id is null and old.project_m_id>0) or
					(new.project_m_id>0 and old.project_m_id is null) or
					(new.project_m_id is null and old.project_m_id is null)
				then 
					-- ## Update for New Project ##
					using_id=new.project_m_id;
					Update kdvn_project set project_amount=(Select
											sum(coalesce(q_amount_total_m,0)) as project_amount
										from 
											sale_order
										where 
											state='done' and project_m_id=using_id
										group by 
											project_m_id)
					where id=using_id;
					Update kdvn_project set project_vat=(Select
											sum(coalesce(q_amount_total_m,0))*max(coalesce(taxes_percent,0))/100 as project_vat
										from 
											sale_order
										where 
											state='done' and project_m_id=using_id
										group by 
											project_m_id)
					where id=using_id;
					Update kdvn_project set project_total=(Select
											sum(coalesce(q_amount_total_m,0))*(max(coalesce(taxes_percent,0))/100+1)
										from 
											sale_order
										where 
											state='done' and project_m_id=using_id
										group by 
											project_m_id)
					where id=using_id;

					-- ## Update for Old Project ##					
					using_id=old.project_m_id;
					Update kdvn_project set project_amount=(Select
											sum(coalesce(q_amount_total_m,0)) as project_amount
										from 
											sale_order
										where 
											state='done' and project_m_id=using_id
										group by 
											project_m_id)
					where id=using_id;
					Update kdvn_project set project_vat=(Select
											sum(coalesce(q_amount_total_m,0))*max(coalesce(taxes_percent,0))/100 as project_vat
										from 
											sale_order
										where 
											state='done' and project_m_id=using_id
										group by 
											project_m_id)
					where id=using_id;
					Update kdvn_project set project_total=(Select
											sum(coalesce(q_amount_total_m,0))*(max(coalesce(taxes_percent,0))/100+1)
										from 
											sale_order
										where 
											state='done' and project_m_id=using_id
										group by 
											project_m_id)
					where id=using_id;
				end if;
--########## End of Mechanical ##########

--########## Electrical ##########
				--Update Project Amount, VAT, Total E when change Approved Amount E
				if new.q_amount_total_e<>old.q_amount_total_e or new.state<>old.state then 
					using_id=new.project_e_id;
					Update kdvn_project set project_amount=(Select
											sum(coalesce(q_amount_total_e,0)) as project_amount
										from 
											sale_order
										where 
											state='done' and project_e_id=using_id
										group by 
											project_e_id)
					where id=using_id;
					Update kdvn_project set project_vat=(Select
											sum(coalesce(q_amount_total_e,0))*max(coalesce(taxes_percent,0))/100 as project_vat
										from 
											sale_order
										where 
											state='done' and project_e_id=using_id
										group by 
											project_e_id)
					where id=using_id;
					Update kdvn_project set project_total=(Select
											sum(coalesce(q_amount_total_e,0))*(max(coalesce(taxes_percent,0))/100+1)
										from 
											sale_order
										where 
											state='done' and project_e_id=using_id
										group by 
											project_e_id)
					where id=using_id;
				end if;
				-- ## Update Project Amount, VAT, Total M when change Project ID ##
				if
					(new.project_e_id<>old.project_e_id) or
					(new.project_e_id is null and old.project_e_id>0) or
					(new.project_e_id>0 and old.project_e_id is null) or
					(new.project_e_id is null and old.project_e_id is null)
				then
				--if new.project_e_id<>old.project_e_id then 
					-- ## Update for New Project ##
					using_id=new.project_e_id;
					Update kdvn_project set project_amount=(Select
											sum(coalesce(q_amount_total_e,0)) as project_amount
										from 
											sale_order
										where 
											state='done' and project_e_id=using_id
										group by 
											project_e_id)
					where id=using_id;
					Update kdvn_project set project_vat=(Select
											sum(coalesce(q_amount_total_e,0))*max(coalesce(taxes_percent,0))/100 as project_vat
										from 
											sale_order
										where 
											state='done' and project_e_id=using_id
										group by 
											project_e_id)
					where id=using_id;
					Update kdvn_project set project_total=(Select
											sum(coalesce(q_amount_total_e,0))*(max(coalesce(taxes_percent,0))/100+1)
										from 
											sale_order
										where 
											state='done' and project_e_id=using_id
										group by 
											project_e_id)
					where id=using_id;

					-- ## Update for Old Project ##					
					using_id=old.project_e_id;
					Update kdvn_project set project_amount=(Select
											sum(coalesce(q_amount_total_e,0)) as project_amount
										from 
											sale_order
										where 
											state='done' and project_e_id=using_id
										group by 
											project_e_id)
					where id=using_id;
					Update kdvn_project set project_vat=(Select
											sum(coalesce(q_amount_total_e,0))*max(coalesce(taxes_percent,0))/100 as project_vat
										from 
											sale_order
										where 
											state='done' and project_e_id=using_id
										group by 
											project_e_id)
					where id=using_id;
					Update kdvn_project set project_total=(Select
											sum(coalesce(q_amount_total_e,0))*(max(coalesce(taxes_percent,0))/100+1)
										from 
											sale_order
										where 
											state='done' and project_e_id=using_id
										group by 
											project_e_id)
					where id=using_id;
				end if;
--########## End of Electrical ##########
                           -- end if; -- ### End Of Update Trigger ###
                            
-- ### Begin Of Insert Trigger ###
                            ELSEIF TG_OP='INSERT' then
                                if new.approved_amount>0 then
                                    update sale_order set negotiating='Fixed' where id=new.id;
                                else
                                    update sale_order set negotiating='Negotiated' where id=new.id;
                                end if;
                                --Mechanical
                                if new.state='done' and new.project_m_id>0 then --Update Project Amount, VAT, Total M WHEN change Project M ID
					using_id=new.project_m_id;
					Update kdvn_project set project_amount=(Select
											sum(coalesce(q_amount_total_m,0)) as project_amount
										from 
											sale_order
										where 
											state='done' and project_m_id=using_id
										group by 
											project_m_id)
					where id=using_id;
					Update kdvn_project set project_vat=(Select
											sum(coalesce(q_amount_total_m,0))*max(coalesce(taxes_percent,0))/100 as project_vat
										from 
											sale_order
										where 
											state='done' and project_m_id=using_id
										group by 
											project_m_id)
					where id=using_id;
					Update kdvn_project set project_total=(Select
											sum(coalesce(q_amount_total_m,0))*(max(coalesce(taxes_percent,0))/100+1)
										from 
											sale_order
										where 
											state='done' and project_m_id=using_id
										group by 
											project_m_id)
					where id=using_id;
				end if;
				
				--Electrical
				if new.state='done' and new.project_e_id>0 then --Update Project Amount, VAT, Total E WHEN change Project E ID
					using_id=new.project_e_id;
					Update kdvn_project set project_amount=(Select
											sum(coalesce(q_amount_total_e,0)) as project_amount
										from 
											sale_order
										where 
											state='done' and project_e_id=using_id
										group by 
											project_e_id)
					where id=using_id;
					Update kdvn_project set project_vat=(Select
											sum(coalesce(q_amount_total_e,0))*max(coalesce(taxes_percent,0))/100 as project_vat
										from 
											sale_order
										where 
											state='done' and project_e_id=using_id
										group by 
											project_e_id)
					where id=using_id;
					Update kdvn_project set project_total=(Select
											sum(coalesce(q_amount_total_e,0))*(max(coalesce(taxes_percent,0))/100+1)
										from 
											sale_order
										where 
											state='done' and project_e_id=using_id
										group by 
											project_e_id)
					where id=using_id;
				end if;
                            end if;-- ### End Of Insert Trigger ###

                            if TG_OP='DELETE' then
                                --Mechanical
                                if old.state='done' and old.project_m_id>0 then --Update Project Amount, VAT, Total M WHEN change Project M ID
					using_id=old.project_m_id;
					Update kdvn_project set project_amount=(Select
											sum(coalesce(q_amount_total_m,0)) as project_amount
										from 
											sale_order
										where 
											state='done' and project_m_id=using_id
										group by 
											project_m_id)
					where id=using_id;
					Update kdvn_project set project_vat=(Select
											sum(coalesce(q_amount_total_m,0))*max(coalesce(taxes_percent,0))/100 as project_vat
										from 
											sale_order
										where 
											state='done' and project_m_id=using_id
										group by 
											project_m_id)
					where id=using_id;
					Update kdvn_project set project_total=(Select
											sum(coalesce(q_amount_total_m,0))*(max(coalesce(taxes_percent,0))/100+1)
										from 
											sale_order
										where 
											state='done' and project_m_id=using_id
										group by 
											project_m_id)
					where id=using_id;
				end if;
				--Electrical
				if old.state='done' and old.project_e_id>0 then --Update Project Amount, VAT, Total E WHEN change Project E ID
					using_id=old.project_e_id;
					Update kdvn_project set project_amount=(Select
											sum(coalesce(q_amount_total_e,0)) as project_amount
										from 
											sale_order
										where 
											state='done' and project_e_id=using_id
										group by 
											project_e_id)
					where id=using_id;
					Update kdvn_project set project_vat=(Select
											sum(coalesce(q_amount_total_e,0))*max(coalesce(taxes_percent,0))/100 as project_vat
										from 
											sale_order
										where 
											state='done' and project_e_id=using_id
										group by 
											project_e_id)
					where id=using_id;
					Update kdvn_project set project_total=(Select
											sum(coalesce(q_amount_total_e,0))*(max(coalesce(taxes_percent,0))/100+1)
										from 
											sale_order
										where 
											state='done' and project_e_id=using_id
										group by 
											project_e_id)
					where id=using_id;
				end if;
                            end if;
                        Return new;
                        end;    
                        $BODY$
                      LANGUAGE plpgsql VOLATILE
                      COST 100;